from PyQt5 import QtWidgets, QtSql
from models.user import User


def createConnection():
    if QtSql.QSqlDatabase.contains("qt_sql_default_connection"):
        db = QtSql.QSqlDatabase.database("qt_sql_default_connection")
    else:
        db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName("helloword")
    if not db.open():
        QtWidgets.QMessageBox.critical(None, "Cannot open database",
                             "Unable to establish a database connection.\n"
                             "This example needs SQLite support. Please read "
                             "the Qt SQL driver documentation for information how "
                             "to build it.\n\n"
                             "Click Cancel to exit.", QtWidgets.QMessageBox.Cancel)
        return False
    query = QtSql.QSqlQuery()

    query.exec_("""CREATE TABLE IF NOT EXISTS "user"
                (
                    uid integer not null
                        constraint user_pk
                            primary key autoincrement,
                    username varchar(20),
                    password varchar(255),
                    email varchar(50),
                    exam int
                );""")
    # query.exec_("""INSERT INTO user VALUES(1,'123','202cb962ac59075b964b07152d234b70','@',0)""")

def new_user(username, password, email, exam):
    query = QtSql.QSqlQuery()
    # query.prepare("INSERT INTO person (id, forename, surname) "
    #               "VALUES (:id, :forename, :surname)")
    register_user = User(username, password, email, exam)
    # insert_sql = "insert into user(uid,username,password,email,exam) values(\'"+username+'\''+','+'\''+register_user.password+'\''+','+'\''+email+'\''+','+'\''+str(exam)+'\''+')'
    query.prepare("insert into user(uid,username,password,email,exam) values(null,:username,:password,:email,:exam)")
    # query.bindValue(":uid",None)
    query.bindValue(":username", username)
    query.bindValue(":password", register_user.password)
    query.bindValue(":email", email)
    query.bindValue(":exam", exam)
    if query.exec_():
        print('succeed insert data')

if __name__ == '__main__':
    new_user("aaa","123","@",1)
    # pass
    # createConnection()
    # db.open()
    # q = QtSql.QSqlQuery()
    # sql_code = 'select * from person'
    # if q.exec(sql_code):
    #     id_index = q.record().indexOf('id')
    #     firstname_index = q.record().indexOf('firstname')
    #     lastname_index = q.record().indexOf('lastname')
    #     while q.next():
    #         id = q.value(id_index)
    #         name = q.value(firstname_index)
    #         age = q.value(lastname_index)
    #         print(id, name, age)
